package retrival;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class jdbcmysql {
  private Connection con = null; //Database objects
  //硈钡object
  private Statement stat = null;
  //磅︽,肚ぇsqlЧ俱﹃
  private ResultSet rs = null;
  //挡狦栋
  private PreparedStatement pst = null;
  //磅︽,肚ぇsql箇纗ぇビ,惠璶肚跑计ぇ竚
  //ノ?ㄓ暗夹ボ

  private int RecordCount = 0;
  private int TestingRecordCount = 0;
  private int TestRecordCount = 0;
  private int PriceCount=0;
  private double Capital = 0;
  private double TotalReturn = 0;





  public jdbcmysql()
  {
    try {
      Class.forName("com.mysql.jdbc.Driver");
      //爹driver
      con = DriverManager.getConnection(
      "jdbc:mysql://localhost/dss?useUnicode=true&characterEncoding=Big5",
      "root","123456");
      //眔connection

//jdbc:mysql://localhost/test?useUnicode=true&characterEncoding=Big5
//localhost琌诀,test琌database
//useUnicode=true&characterEncoding=Big5ㄏノ絪絏

    }
    catch(ClassNotFoundException e)
    {
      System.out.println("DriverClassNotFound :"+e.toString());
    }//Τ穦玻ネsqlexception
    catch(SQLException x) {
      System.out.println("Exception :"+x.toString());
    }

  }

  public void initial(){
	  RecordCount = 0;
	  TestingRecordCount = 0;
	  PriceCount=0;
	  Capital = 0;
	  TotalReturn = 0;
	  TestRecordCount = 0;
  }

  public AIReport[] getAIReport() {
	  AIReport[] reports = new AIReport[100];
	  try {
    	stat = con.createStatement();
    	rs = stat.executeQuery("SELECT * FROM `dss_ai_report` WHERE `status`=1 and `type`='A'");

    	while(rs.next())
    		reports[RecordCount++] = new AIReport(rs.getInt("id"), rs.getInt("model"));
	  }catch(SQLException e) {
		  	System.out.println("DropDB Exception :" + e.toString());
	  }finally {
		  	Close();
	  }
	  return reports;
  }

  public AITesting[] getTesingRecord() {
	  AITesting[] reports = new AITesting[100];
	  try {
    	stat = con.createStatement();
    	rs = stat.executeQuery("SELECT a.*, b.model, b.stockid" +
    			" FROM  `dss_ai_report_sim` a "+
    			" LEFT JOIN  `dss_ai_report` b ON a.report_id = b.id"+
    			" WHERE a.`status` =0 AND `end_date` != '0000-00-00'");

    	while(rs.next())
    		reports[TestingRecordCount++] = new AITesting(rs.getInt("report_id"),rs.getInt("id"), rs.getString("stockid"),  rs.getInt("model"),rs.getString("testing_point"),rs.getString("end_date"));
	  }catch(SQLException e) {
		  	System.out.println("DropDB Exception :" + e.toString());
	  }finally {
		  	Close();
	  }
	  return reports;
  }

  public AITesting[] getTestRecord() {
	  AITesting[] reports = new AITesting[100];
	  try {
    	stat = con.createStatement();
    	rs = stat.executeQuery("SELECT a.*, b.model" +
    			" FROM  `dss_ai_report_test` a "+
    			" LEFT JOIN  `dss_ai_report` b ON a.report_id = b.id"+
    			" WHERE a.`status` =0");

    	while(rs.next())
    		reports[TestRecordCount++] = new AITesting(rs.getInt("report_id"),rs.getInt("id"), "", rs.getInt("model"),"","");
	  }catch(SQLException e) {
		  	System.out.println("DropDB Exception :" + e.toString());
	  }finally {
		  	Close();
	  }
	  return reports;
  }

  public StockPrice[] getStockPrice(String stockid, String Day1, String Day2){
	  StockPrice[] prices = new StockPrice[20000];
	  PriceCount = 0;
	  try {
    	stat = con.createStatement();
    	rs = stat.executeQuery("SELECT * FROM  `dss_stock_price` "+
    			" WHERE `stockid` = '"+stockid+"' AND `date` >= '"+Day1+"' AND `date` <= '"+Day2+"'" +
    			" order by `date` ASC");

    	while(rs.next())
    		prices[PriceCount++] = new StockPrice(rs.getString("date"),rs.getFloat("price"));
	  }catch(SQLException e) {
		  	System.out.println("DropDB Exception :" + e.toString());
	  }finally {
		  	Close();
	  }
	  return prices;
  }

  public void insertTestingRecord(int tid, int rid, String bdate, String Sdate, double bp, double sp, double hp, double lp, int numofday){

	  double totalreturn = this.calculateReturn(bp,sp);
	  double p1 = this.Capital;
	  double p2 = Math.round(p1*(100+totalreturn)/100.0);

	  this.TotalReturn = Math.round( this.TotalReturn * (100.0 + totalreturn)  ) / 100.0;


	  this.Capital = p2;
	  try {
	    	stat = con.createStatement();
	    	String SQL = "Insert into `dss_ai_report_sim_record` " +
	    	"( `testing_id` ,`report_id` ,`buy_date` ,`sell_date` ,`num_of_day` ,`buy_price` ,`sell_price` ,`highest_price` ,`lowest_price` ,`final_return` ,`initial_capital` ,`final_capital`, `status`)" +
	    	"VALUES ( '"+tid+"',  '"+rid+"',  '"+bdate+"',  '"+Sdate+"',  '"+numofday+"',  '"+bp+"',  '"+sp+"',  '"+hp+"',  '"+lp+"',  '"+totalreturn+"',  '"+p1+"',  '"+p2+"', '0');";
	    	System.out.print(SQL);
	    	stat.executeUpdate(SQL);

		  }catch(SQLException e) {
			  	System.out.println("UpdateDB Exception :" + e.toString());
		  }finally {
			  	Close();
		  }
  }

  public double calculateReturn(double start_price, double end_price){
		return Math.round((end_price - start_price ) / start_price * 10000) / 100.0;
  }

  public void initCapital(){
	  this.Capital = 10000;
	  TotalReturn = 100;
  }

  public void updateTestingRecord(int tid, int rid, int tCount){
	  try {
		  this.TotalReturn = this.TotalReturn -100;
		  double avg_return;
		  if (tCount > 0)
			  avg_return = this.TotalReturn / tCount;
		  else
			  avg_return = 0;
	    	stat = con.createStatement();
	    	String SQL = "update `dss_ai_report_sim` set `status` = '1', `trading_count` = '"+tCount+"', `return`='"+this.TotalReturn+"', `avg_return` = '"+avg_return+"' where `id` = '"+tid+"'; ";
	    	//System.out.println(SQL);
	    	stat.executeUpdate(SQL);

		  }catch(SQLException e) {
			  	System.out.println("UpdateDB Exception :" + e.toString());
		  }finally {
			  	Close();
		  }
  }

  public void updateTestRecord(int tid, double total, double incon, double kappa, double MSE, double RMSE, double RAE, double RRSE){
	  try {
	    	stat = con.createStatement();
	    	String SQL = "update `dss_ai_report_test` set `status` = '1', `total_instances` = '"+total+"', " +
	    	"`incon_instances`='"+incon+"', `kappa`='"+kappa+"', `MSE`='"+MSE+"', `RMSE`='"+RMSE+"',"+
	    	" `RAE`='"+RAE+"', `RRSE`='"+RRSE+"'  where `id` = '"+tid+"'; ";

	    	stat.executeUpdate(SQL);

		  }catch(SQLException e) {
			  	System.out.println("UpdateDB Exception :" + e.toString());
		  }finally {
			  	Close();
		  }
  }

  public void saveResult(int id, double precision, int action){

	  String action_label = "N";
	  if (action == 0)
		  action_label = "B";
	  else if (action == 1)
		  action_label = "N";
	  else if (action == 2)
		  action_label = "S";

	  try {
    	stat = con.createStatement();
    	String SQL = "Update `dss_ai_report` set " +
		"`accuracy` = '"+precision+"', " +
		"`action` = '"+action_label+"', " +
		"`status` = '2' WHERE `id`='"+id+ "';";

    	stat.executeUpdate(SQL);

	  }catch(SQLException e) {
		  	System.out.println("UpdateDB Exception :" + e.toString());
	  }finally {
		  	Close();
	  }

  }

  public int getReportCount(){
	  return RecordCount;
  }

  public int getTestingRecordCount(){
	  return TestingRecordCount;
  }

  public int getTestRecordCount(){
	  return TestRecordCount;
  }

  public int getPriceCount(){
	  return PriceCount;
  }

  private void Close()
  {
    try
    {
      if(rs!=null)
      {
        rs.close();
        rs = null;
      }
      if(stat!=null)
      {
        stat.close();
        stat = null;
      }
      if(pst!=null)
      {
        pst.close();
        pst = null;
      }
    }
    catch(SQLException e)
    {
      System.out.println("Close Exception :" + e.toString());
    }
  }

}